﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml;
using MySQL.Data;
using SQLServer.Data;
using System.Data.OleDb;
using System.IO;
using System.Data;
using MongoDB.Driver;
using MongoDB.Driver.Builders;
using System.Data.SQLite;
using SQLite.Data;

namespace Client.Library
{
    public static class DataImport
    {
        #region Excel Methods

        const string GetExcelSalesCommand = @"SELECT ProductID, Quantity, [Unit Price] 
                                              FROM [Sales$]";

        public static void PushSalesToServer(List<SQLServer.Data.Sale> sales)
        {
            using (SupermarketEntities connection = new SupermarketEntities())
            {
                foreach (var sale in sales)
                {
                    connection.Sales.Add(sale);
                }

                connection.SaveChanges();
            }
        }

        #endregion

        #region XML Methods

        public static void ImportExpenses()
        {
            var connectionString = "mongodb://localhost";
            var client = new MongoClient(connectionString);
            var server = client.GetServer();
            var database = server.GetDatabase("vendor-reports");
            var collection = database.GetCollection<ProductReport>("vendor-reports");

            if (collection.Exists())
            {
                collection.RemoveAll();
            }

            XmlDocument doc = new XmlDocument();
            doc.Load("../../expenses.xml");
            var rootElement = doc.DocumentElement;

            foreach (XmlNode sale in rootElement.ChildNodes)
            {
                XmlAttributeCollection attributes = sale.Attributes;
                var vendor = attributes[0].Value;

                foreach (XmlNode exp in sale.ChildNodes)
                {
                    var month = exp.Attributes[0].Value;
                    decimal expense = decimal.Parse(exp.InnerText);
                    int vendorId;

                    using (SupermarketEntities ctx = new SupermarketEntities())
                    {
                        vendorId = ctx.Vendors.Where(v => v.Name == vendor).First().ID;
                        ctx.Expenses.Add(new Expens
                        {
                            VendorID = vendorId,
                            Month = DateTime.Parse(month),
                            Expenses = expense
                        });
                        ctx.SaveChanges();
                    }

                    var vendorReport = new VendorMonthlyReport
                    {
                        Name = vendor,
                        Month = DateTime.Parse(month),
                        Expenses = expense,
                        VendorId = vendorId
                    };

                    collection.Insert(vendorReport);
                }
            }
        }

        #endregion

        #region MySQL Methods

        public static void ImportMySQLDb()
        {
            //CreateSQLServerDbSchema();

            MySQLModel mySQLCtx = new MySQLModel();
            SupermarketEntities sqlServerCtx = new SupermarketEntities();

            foreach (var vendor in mySQLCtx.Vendors)
            {
                if (sqlServerCtx.Vendors.Any(v => v.ID == vendor.ID))
                {
                    continue;
                }

                SQLServer.Data.Vendor sqlVendor = new SQLServer.Data.Vendor();
                sqlVendor.Name = vendor.Name;
                sqlVendor.ID = vendor.ID;

                sqlServerCtx.Vendors.Add(sqlVendor);
            }

            foreach (var measure in mySQLCtx.Measures)
            {
                if (sqlServerCtx.Measures.Any(m => m.ID == measure.ID))
                {
                    continue;
                }

                SQLServer.Data.Measure sqlMeasure = new SQLServer.Data.Measure();
                sqlMeasure.ID = measure.ID;
                sqlMeasure.Name = measure.Name;

                sqlServerCtx.Measures.Add(sqlMeasure);
            }

            sqlServerCtx.SaveChanges();

            foreach (var product in mySQLCtx.Products)
            {
                if (sqlServerCtx.Products.Any(p => p.ID == product.ID))
                {
                    continue;
                }

                SQLServer.Data.Product sqlProduct = new SQLServer.Data.Product();
                sqlProduct.ID = product.ID;
                sqlProduct.MeasureID = product.MeasureID;
                sqlProduct.VendorID = product.VendorID;
                sqlProduct.ProductName = product.ProductName;
                sqlProduct.BasePrice = product.BasePrice;

                sqlServerCtx.Products.Add(sqlProduct);
            }

            foreach (var supermarket in mySQLCtx.Supermarkets)
            {
                if (sqlServerCtx.Supermarkets.Any(s => s.ID == supermarket.ID))
                {
                    continue;
                }

                SQLServer.Data.Supermarket sqlSupermarket = new SQLServer.Data.Supermarket();
                sqlSupermarket.ID = supermarket.ID;
                sqlSupermarket.Location = supermarket.Location;
                
                sqlServerCtx.Supermarkets.Add(sqlSupermarket);
            }

            sqlServerCtx.SaveChanges();
        }

        //private static void CreateSQLServerDbSchema()
        //{
        //        using (var context = new NorthwindOAEntityDiagrams())
        //{
        //    ISchemaHandler schemaHandler = context.GetSchemaHandler();
        //    if (schemaHandler.DatabaseExists() == false)
        //    {
        //        schemaHandler.CreateDatabase();
        //        schemaHandler.ExecuteDDLScript(schemaHandler.CreateDDLScript());
        //    }
        //    else
        //    {
        //        string ddl = schemaHandler.CreateUpdateDDLScript(new SchemaUpdateProperties());
        //        schemaHandler.ExecuteDDLScript(ddl);
        //    }
        //}
        //}

        #endregion

        #region MongoDB methods

        public static void ImportMongoDbProductReports()
        {
            using (SQLite.Data.SQLiteContext ctx = new SQLite.Data.SQLiteContext())
            {
                Dictionary<string, decimal> vendorsExpenses = GetVendorsMonthlyExpenses();
                Dictionary<string, Tuple<decimal, decimal>> vendorsIncomes = GetVendorsMonthlyIncomes();
                List<ProductsTotalReport> recordsList = new List<ProductsTotalReport>();
                
                foreach (var vendor in vendorsExpenses)
                {
                    ProductsTotalReport record = new ProductsTotalReport();
                    record.Expenses = vendorsExpenses[vendor.Key];
                    record.Incomes = vendorsIncomes[vendor.Key].Item1;
                    record.Taxes = vendorsIncomes[vendor.Key].Item2;
                    record.FinancialResult = record.Incomes - record.Expenses - record.Taxes;
                    record.VendorID = 12;
                    record.VendorName = vendor.Key;

                    ctx.ProductsTotalReport.Add(record);
                    recordsList.Add(record);
                }

                ctx.SaveChanges();
                DataExport.ExportProductReportToXLS(recordsList, "../../test.xls");
            }
        }

        private static Dictionary<string, decimal> GetVendorsMonthlyExpenses()
        {
            Dictionary<string, decimal> vendorsExpenses = new Dictionary<string, decimal>();
            var connectionString = "mongodb://localhost";
            var client = new MongoClient(connectionString);
            var server = client.GetServer();
            var databaseVendors = server.GetDatabase("vendor-reports");
            var collection = databaseVendors.GetCollection<VendorMonthlyReport>("vendor-reports");
            var allRecords = collection.FindAll().ToList();
            List<string> vendorNames = new List<string>();
            using (SupermarketEntities db = new SupermarketEntities())
            {
                foreach (var vendor in db.Vendors.Select(v => v.Name))
                {
                    vendorNames.Add(vendor);
                }
            }

            foreach (var vendorName in vendorNames)
            {
                var currentMonthVendorRecords = allRecords.Where(v => v.Month.Month == DateTime.Now.Month
                    && v.Month.Year == DateTime.Now.Year && v.Name == vendorName);
                decimal vendorExpenseMoney = 0;

                foreach (var record in currentMonthVendorRecords)
                {
                    vendorExpenseMoney += record.Expenses;
                }

                vendorsExpenses.Add(vendorName, vendorExpenseMoney);
            }

            return vendorsExpenses;
        }

        private static Dictionary<string, Tuple<decimal, decimal>> GetVendorsMonthlyIncomes()
        {
            Dictionary<string, Tuple<decimal, decimal>> vendorsIncome = new Dictionary<string, Tuple<decimal, decimal>>();

            using (SupermarketEntities db = new SupermarketEntities())
            {
                foreach (var vendor in db.Vendors.Include("Products"))
                {
                    decimal vendorIncome = 0;
                    decimal vendorTaxes = 0;
                    decimal currentProductIncome = 0;

                    foreach (var product in vendor.Products)
                    {
                        var sales = product.Sales
                            .Where(s => s.OrderDate.Month == DateTime.Now.Month
                                && s.OrderDate.Year == DateTime.Now.Year);

                        foreach (var sale in sales)
                        {
                            currentProductIncome = sale.Quantity * product.BasePrice;
                            vendorIncome += currentProductIncome;
                        }

                        using (SQLite.Data.SQLiteContext sqliteDb = new SQLite.Data.SQLiteContext())
                        {
                            var productWithTax = sqliteDb.ProductsAndTaxes.Where(p => p.ProductID == product.ID).FirstOrDefault();
                            decimal tax = productWithTax.Tax;
                            vendorTaxes += tax * currentProductIncome;
                        }
                    }

                    

                    vendorsIncome.Add(vendor.Name, new Tuple<decimal, decimal>(vendorIncome, vendorTaxes));
                }
            }

            return vendorsIncome;
        }

        #endregion
    }
}
